"""
Case Type   : interval
Case Name   : gs_dump导入导出interval表达式
Create At   : 2024/10/8
Owner       : @zhanghuan96
Description :
    1.创建兼容b库数据库
    2.建表并插入数据
    3.创建视图
    4.使用gs_dump工具导出表为自定义归档格式
    5.导入之前导出的数据到新库
    6.连接导入库查询
    7.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.成功
History     :
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class Security(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.common = Common()
        self.sh_pri = CommonSH('PrimaryDbUser')
        self.dbuser_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = 'db_dolphin_0036'
        self.db_name_01 = 'db_dolphin_0036_01'
        self.tb_name = 'tb_dolphin_0036'
        self.v_name = 'v_dolphin_0036'
        self.dump_path = os.path.join(macro.DB_BACKUP_PATH, 'dump0036')
        self.expect_result_dict = {
            'Column': ['c1', 'c2', 'c3', 'c4', 'c5', 'c6', 'c7', 'c8', 'c9',
                       'c10', 'c11', 'c12', 'c13', 'c14', 'c15', 'c16', 'c17',
                       'c18', 'c19', 'c20', 'c21', 'c22', 'c23', 'c24', 'c25',
                       'c26', 'c27', 'c28', 'timestamp4', 'c30', 'c31',
                       'c32', 'c33', 'c34', 'c35', 'c36'],
            'Type': ['tinyint', 'uint1', 'smallint', 'uint2', 'integer',
                     'uint4', 'integer', 'uint4', 'bigint', 'uint8', 'real',
                     'real', 'double precision', 'double precision',
                     'double precision', 'numeric(20,6)', 'numeric(20,6)',
                     'numeric(20,6)', 'numeric(20,6)', 'bit(1)', 'bit(64)',
                     'boolean', 'date', 'time(0) without time zone',
                     'time(4) without time zone',
                     'timestamp(0) without time zone',
                     'timestamp(4) without time zone',
                     'timestamp(0) with time zone',
                     'timestamp(4) with time zone', 'year', 'character(100)',
                     'character varying(100)', '"binary"(100)',
                     '"varbinary"(100)', 'text', 'integer']}

    def test_odbc(self):
        text = '-----step1:创建兼容b库数据库;expect:成功-----'
        self.log.info(text)
        sql_cmd = (f"drop database if exists {self.db_name};"
                   f"create database {self.db_name} dbcompatibility ='B';"
                   f"drop database if exists {self.db_name_01};"
                   f"create database {self.db_name_01} dbcompatibility ='B';")
        self.log.info(sql_cmd)
        sql_res = self.sh_pri.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_res,
                      '执行失败' + text)

        text = '-----step2:建表并插入数据;expect:成功-----'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name};
        create table {self.tb_name}(
        c1 tinyint comment '数值',
        c2 tinyint unsigned,
        c3 smallint,
        c4 smallint unsigned,
        c5 integer,
        c6 integer unsigned,
        c7 mediumint,
        c8 mediumint unsigned,
        c9 bigint,
        c10 bigint unsigned,
        c11 float,
        c12 float4,
        c13 float8,
        c14 double,
        c15 double precision,
        c16 numeric(20,6),
        c17 decimal(20,6),
        c18 fixed(20,6),
        c19 number(20,6),
        c20 bit(1),
        c21 bit(64),
        c22 boolean,
        c23 date,
        c24 time,
        c25 time(4),
        c26 datetime,
        c27 datetime(4),
        c28 timestamp,
        \`timestamp4\` timestamp(4),
        c30 year,
        c31 char(100),
        c32 varchar(100),
        c33 binary(100),
        c34 varbinary(100),
        c35 text,
        c36 integer
        );
        insert into {self.tb_name} values(
        1,
        1,
        1,
        1,
        1,
        1,
        1,
        1,
        1,
        1,
        1.0,
        1.0,
        1.0,
        125.689,
        3.14259,
        3.14259,
        3.14259,
        3.14259,
        3.14259,
        1,
        10101100,
        1,
        '2024-01-11',
        '11:47:58',
        '11:47:58.7896',
        '2024-01-11 11:49:25',
        '2024-01-11 11:49:25.1234',
        '2024-01-11 11:49:25',
        '2024-01-11 11:49:25.1234',
        '2024',
        '2024-01-11 11:49:25',
        '2024-01-11 11:49:25',
        '2024-01-11 11:49:25',
        '2024-01-11 11:49:25',
        '2024-01-11 11:49:25',
        null);
        '''
        self.log.info(sql_cmd)
        sql_res = self.sh_pri.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('CREATE TABLE', sql_res, '查询失败')
        self.assertIn('INSERT 0 1', sql_res, '执行失败' + text)

        text = '-----step3:创建视图;expect:成功-----'
        self.log.info(text)
        sql_cmd = f'''drop view if exists {self.v_name};
        create view {self.v_name} as select
        c1+ interval 1 day as 'int + interval 1 day',
        c2 + interval 1 year as 'uint1 + interval 1 year',
        c3 + interval 1 quarter as 'int2 + interval 1 quarter',
        c4 + interval 1 month as 'uint2 + interval 1 month',
        c5+ interval 1 week as 'int4 + interval 1 week',
        c6 + interval 1 hour as 'uint4 + interval 1 hour',
        c7 + interval 1 minute as 'mediumint4 + interval 1 minute',
        c8 + interval 1 second as 'm_uint4 + interval 1 second',
        c9 + interval 1 microsecond as 'int8 + interval 1 microsecond',
        c11 + interval 1 year_month as 'float + interval 1 year_month',
        c12 + interval 1 day_hour as 'float4 + interval 1 day_hour',
        c13 + interval 1 day_minute as 'float8 + interval 1 day_minute',
        c14 + interval 1 day_second as 'double + interval 1 day_second',
        c15 + interval 1 day_microsecond as 'double precision \
        + interval 1 day_microsecond',
        c16 + interval 1 hour_minute as 'numeric + interval 1 \
        hour_minute',
        c17 + interval 1 hour_second as 'decimal + interval 1 \
        hour_second',
        c18 + interval 1 hour_microsecond as 'fixed + interval 1 \
        hour_microsecond',
        c19 + interval 1 minute_second as 'number + interval 1 \
        minute_second',
        c20 + interval 1 minute_microsecond as 'bit1 + interval 1 \
        minute_microsecond',
        c21 + interval 1 second_microsecond as 'bit64 + interval 1 \
        second_microsecond',
        c22 + interval 1 day as 'boolean + interval 1 day',
        c23 + interval 1 day as 'date + interval 1 day',
        c24 + interval 1 day as 'time + interval 1 day',
        c25 + interval 1 day as 'time4 + interval 1 day',
        c26 + interval 1 day as 'datetime + interval 1 day',
        c27 + interval 1 day as 'datetime4 + interval 1 day',
        c28 + interval 1 day as 'timestamp + interval 1 day',
        \`timestamp4\` + interval 1 day as 'timestamp4 + interval 1 day',
        c30 + interval 1 day as 'year + interval 1 day',
        c31 + interval 1 day as 'char + interval 1 day',
        c32 + interval 1 day as 'varchar + interval 1 day',
        c33 + interval 1 day as 'binary + interval 1 day',
        c34 + interval 1 day as 'varbinary + interval 1 day',
        c35 + interval 1 day as 'text + interval 1 day',
        c36 + interval 1 day as 'null + interval 1 day'
        from {self.tb_name};
        '''
        self.log.info(sql_cmd)
        sql_res = self.sh_pri.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('CREATE VIEW', sql_res, '查询失败')

        text = '----step4: 使用gs_dump工具导出表为自定义归档格式 expect: 成功----'
        self.log.info(text)
        gs_dump_cmd = f"source {macro.DB_ENV_PATH};" \
                      f"gs_dump " \
                      f"-p {self.dbuser_node.db_port} " \
                      f"{self.db_name} -F c " \
                      f"-f {self.dump_path}"
        self.log.info(gs_dump_cmd)
        dump_msg = self.dbuser_node.sh(gs_dump_cmd).result()
        self.log.info(dump_msg)
        self.assertIn(f"dump database {self.db_name} successfully",
                      dump_msg, '执行失败:' + text)

        text = '----step5: 导入之前导出的数据到新库 expect: 成功----'
        self.log.info(text)
        gs_restore_cmd = f"source {macro.DB_ENV_PATH};" \
                         f"gs_restore -p {self.dbuser_node.db_port} " \
                         f"-d {self.db_name_01} " \
                         f"{self.dump_path}"
        self.log.info(gs_restore_cmd)
        restore_msg = self.dbuser_node.sh(gs_restore_cmd).result()
        self.log.info(restore_msg)
        self.assertIn(self.constant.RESTORE_SUCCESS_MSG, restore_msg,
                      '执行失败:' + text)

        text = '----step6: 连接导入库查询 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"\d+ {self.tb_name};"
        self.log.info(sql_cmd)
        sql_result = self.sh_pri.execut_db_sql(sql_cmd,
                                               dbname=self.db_name_01)
        self.log.info(sql_result)
        result_dict = self.common.format_sql_result(sql_result)
        self.log.info(result_dict)
        del_key = ['Modifiers', 'Storage', 'Stats target', 'Description']
        for k in del_key:
            result_dict.pop(k, None)
        self.assertDictEqual(self.expect_result_dict, result_dict)

        sql_cmd = f"select * from {self.v_name};"
        sql_result = self.sh_pri.execut_db_sql(sql_cmd,
                                               dbname=self.db_name_01)
        self.log.info(sql_result)
        msg_lis = ['', '', '', '', '', '', '', '', '', '', '', '',
                   '2000-01-25 00:00:01.689', '', '', '', '', '', '', '', '',
                   '2024-01-12', '35:47:58', '35:47:58.7896',
                   '2024-01-12 11:49:25', '2024-01-12 11:49:25.1234',
                   '2024-01-12 11:49:25+08', '2024-01-12 11:49:25.1234+08',
                   '2024', '2024-01-12 11:49:25', '2024-01-12 11:49:25',
                   '2024-01-12 11:49:25', '2024-01-12 11:49:25',
                   '2024-01-12 11:49:25', '']
        for msg in msg_lis:
            self.assertIn(msg, sql_result, '查询失败')

    def tearDown(self):
        text = '---step7: 清理环境 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"drop database if exists {self.db_name_01};"
        self.log.info(sql_cmd)
        sql_result = self.sh_pri.execut_db_sql(sql_cmd,
                                               dbname='postgres')
        self.log.info(sql_result)
        rm_cmd = f"rm -rf {self.dump_path}"
        self.log.info(rm_cmd)
        rm_msg = self.dbuser_node.sh(rm_cmd).result()
        self.log.info(rm_msg)
        self.assertEqual(2,
                         sql_result.count(self.constant.DROP_DATABASE_SUCCESS))
        self.assertEqual('', rm_msg)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
